package com.auxgroup.smarthome.inner.repo;

import com.auxgroup.bridge.app.inner.vo.DeviceModelSimpleVo;
import com.auxgroup.smarthome.inner.entity.DeviceModelInfoEntity;
import com.auxgroup.smarthome.repo.GenericDao;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;


/**
 * Created by niuGuangzhe on 2017/7/25.
 */
public interface DeviceModelInfoRepo  extends GenericDao<DeviceModelInfoEntity, String>{

    //根据型号查找[型号唯一]
    Page<DeviceModelInfoEntity> findByModelAndTrashFalse(String model, Pageable pageable);

    List<DeviceModelInfoEntity> findByModelAndTrashFalse(String model);

    @Query("select new com.auxgroup.bridge.app.inner.vo.DeviceModelSimpleVo(d.modelId,d.model, d.modelItem) from DeviceModelInfoEntity d where d.trash = 0 and d.deviceType = 1")
    List<DeviceModelSimpleVo> getAllNewDeviceModel();

    List<DeviceModelInfoEntity> findByModelItemAndDeviceTypeAndTrashFalse(String modelItem, int deviceType);

    List<DeviceModelInfoEntity> findByModelItemInAndDeviceTypeAndTrashFalse(List<String> modelItems, int deviceType);

    /**
     * 获取所有型号列表
     * @return
     */
    List<DeviceModelInfoEntity> findAllByTrashFalse();

    //根据内机物料型号查找[物料唯一]
    Page<DeviceModelInfoEntity> findByModelItemAndTrashFalse(String modelItem, Pageable pageable);

    DeviceModelInfoEntity findByModelItem(String modelItem);

    //根据主键查找
    DeviceModelInfoEntity findByModelIdAndTrashFalse(String modelId);

    //或操作查询优化
    Page<DeviceModelInfoEntity> findDistinctByModelOrModelItemAndTrashFalse(String model, String modelItem, Pageable pageable);

    //分组获得所有的物料信息
    @Query("select distinct o.modelItem from DeviceModelInfoEntity o group by o.modelItem")
    List<String> findAllModelItems();


//    //根据关键字获取物料信息
//    @Query("select distinct o.modelItem from DeviceModelInfoEntity o where o.modelItem like ?1")
//    List<String> findModelItemsByKeyword(String keyword);


    //逻辑删除
    @Modifying
    @Query("update DeviceModelInfoEntity o set o.trash=true where o.modelId=?1")
    @Transactional(readOnly = false)
    int logicallyDeleted(String modelId);

    /**
     * 查询modelId下的Sn存不存在
     * @param modelId
     * @return
     */
    @Query(value = "SELECT COUNT(*) FROM device_sn_model_mapping dsmm LEFT JOIN device_model_info dmi ON (dmi.is_trash = 0 AND dmi.model_item = dsmm.model_item) WHERE  dmi.model_id = ?1 AND dsmm.sn = ?2" , nativeQuery = true)
    long countSnByModelId(String modelId, String sn);


}
